<html>
<head>
  <title>性能优化(6)-存储优化</title>
  <basefont face="微软雅黑" size="2" />
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  <meta name="exporter-version" content="YXBJ Windows/601935 (zh-CN, DDL); Windows/10.0.0 (Win64); EDAMVersion=V2;"/>
  <style>
    body, td {
      font-family: 微软雅黑;
      font-size: 10pt;
    }
  </style>
</head>
<body>
<a name="717"/>
<h1>性能优化(6)-存储优化</h1>

<div>
<span><div style="min-width: 100%; position: relative;"><div style="outline: 0px; box-sizing: inherit; text-size-adjust: 100%; -webkit-font-smoothing: antialiased; tab-size: 4;"><div style="min-width: 1200px; outline: 0px; box-sizing: inherit; background-color: rgb(245, 246, 247); -webkit-font-smoothing: antialiased; tab-size: 4; zoom: 1;"><div style="outline: 0px; box-sizing: inherit; margin: 0px; padding: 8px 12px 0px; display: flex; -webkit-box-pack: center; justify-content: center; height: 100%;"><div style="outline: 0px; margin: 0px 0px 0px 8px; padding: 0px; box-sizing: border-box; width: 1318px;"><div style="float: right; box-sizing: inherit; outline: 0px; margin: 0px; padding: 0px; width: 1010px;"><div style="outline: 0px; box-sizing: inherit; margin: 0px; position: relative; background: rgb(255, 255, 255); padding: 0px 24px 16px;"><div style="box-sizing: inherit; outline: 0px; margin: 0px; padding: 16px 0px 0px; position: relative;"><div style="outline: 0px; box-sizing: inherit; margin: 0px; padding: 0px; overflow-wrap: break-word;"><div style="outline: 0px; box-sizing: inherit; margin: 0px; padding: 0px; overflow-wrap: break-word; position: relative; min-height: 70px; word-break: break-word;"><div style="outline: 0px; padding: 0px; overflow-wrap: break-word; box-sizing: border-box; margin: 0px 0px 16px;"><div style="font-size: 16px; min-width: 100%; position: relative;"><div style="outline: none; font-size: 14px; background: rgb(255, 255, 255);"><div style="outline:none;background-color:rgb(243, 243, 243);"><div style="outline:none;"><div style="outline:none;background:rgb(255, 255, 255);"><div style="outline:none;background-color:rgb(243, 243, 243);"><div style="outline:none;background:rgb(255, 255, 255);zoom:1;"><h1 style="outline: none; margin: 0px; overflow: hidden; padding: 0px 5px 5px; font-size: 20px;"><span style="font-size: 20pt;">性能优化(6)-存储优化</span></h1><div><span style="font-size: 18px; color: rgb(0, 0, 0); font-family: 宋体; font-weight: bold; line-height: 1.8;">本篇文章是码牛学院经网上整理收集给大家的性能优化全集，希望能帮助到各位同学</span></div><h1 style="outline: none; margin: 0px; overflow: hidden; padding: 0px 5px 5px; font-size: 20px;"><span style="outline: none; overflow: hidden; font-size: 20px;">交换数据格式</span></h1><div style="outline: none; padding: 0px; text-align: left; clear: both; font-size: 16px; overflow-wrap: break-word; word-break: break-all; overflow: hidden; margin: 0px auto;"><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">Google 推出的 Protocal Buffers 是一种更轻便高效的存储结构，但消耗内存较大。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><a href="https://github.com/google/flatbuffers" style="outline: none; font-size: 16px; color: rgb(66, 139, 202); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">FlatBuffers</a> <span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">同样由 Google 推出，专注性能，适合移动端。占用存储比 Protocal 要大。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">SharePreferences 优化</span></div></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">当 SharedPreferences 文件还没有被加载到内存时，调用 getSharedPreferences 方法会初始化文件并读入内存，这容易导致 耗时更长。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">Editor 的 commit 或者 apply 方法的区别在于同步写入和异步 写入，以及是否需要返回值。在不需要返回值的情况下，使用 apply 方法可以极大提高性能。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">SharedPreferences 类 中的 commitToMemory() 会锁定 SharedPreference 对象，put() 和 getEditor() 方法会锁定 Editor 对象，在写入磁盘时更会锁定一个写入锁。因此，最好的优化方法就是避免频繁地读写 SharedPreferences，减少无谓的调用。对于 SharedPreferences 的批量操作，最好先获取一个 editor 进行批量操作，然后调用 apply 方法。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">Bitmap 解码</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">4.4 以上 decodeFile 内部没有使用缓存，效率不高。要使用 decodeStream，同时传入的文件流为 BufferedInputStream。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">decodeResource 同样存在性能问题，用 decodeResourceStream。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">数据库优化</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">1、使用 StringBuilder 代替 String</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">2、查询时返回更少的结果集及更少的字段</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">查询时只取需要的字段和结果集，更多的结果集会消耗更多的时间及内存，更多的字段会导致更多的内存消耗。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">3、少用 cursor.getColumnIndex</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">根据性能调优过程中的观察 cursor.getColumnIndex 的时间消耗跟 cursor.getInt 相差无几。可以在建表的时候用 static 变量记住某列的 index，直接调用相应 index 而不是每次查询。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">4、异步线程</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">Android 中数据不多时表查询可能耗时不多，不会导致 ANR，不过大于 100ms 时同样会让用户感觉到延时和卡顿，可以放在线程中运行，但 sqlite 在并发方面存在局限，多线程控制较麻烦，这时候可使用单线程池，在任务中执行 db 操作，通过 handler 返回结果和 UI 线程交互，既不会影响 UI 线程，同时也能防止并发带来的异常。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">5、SQLiteOpenHelper</span> <span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">维持一个单例</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">因为 SQLite 对多线程的支持并不是很完善，如果两个线程同时操作数据库，因为数据库被另一个线程占用， 这种情况下会报“Database is locked” 的异常。所以在数据库管理类中使用单例模式，就可以保证无论在哪个线程中获取数据库对象，都是同一个。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">最好的方法是所有的数据库操作统一到同一个线程队列管理，而业务层使用缓存同步，这样可以完全避免多线程操作数据库导致的不同步和死锁问题。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">6、Application 中初始化</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">使用 Application 的 Context 创建数据库，在 Application 生命周期结束时再关闭。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">在应用启动过程中最先初始化完数据库，避免进入应用后再初始化导致相关操作时间变长。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">7、少用 AUTOINCREMENT</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">主键加上 AUTOINCREMENT 后，可以保证主键严格递增，但并不能保证每次都加 1，因为在插入失败后，失败的行号不会被复用，会造成主键有间隔，继而使 INSERT 耗时 1 倍以上。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">这个 AUTOINCREMENT 关键词会增加 CPU，内存，磁盘空间和磁盘 I/O 的负担，所以 尽量不要用，除非必需。通常情况下都不是必需的。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">事务</span></div></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">使用事务的两大好处是原子提交和更优性能：</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">原子提交：意味着同一事务内的所有修改要么都完成要么都不做，如果某个修改失败，会自动回滚使得所有修改不生效。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">更优性能：Sqlite 默认会为每个插入、更新操作创建一个事务，并且在每次插入、更新后立即提交。这样如果连续插入 100 次数据实际是创建事务、执行语句、提交这个过程被重复执行了 100 次。如果显式的创建事务，这个过程只做一次，通过这种一次性事务可以使得性能大幅提升。尤其当数据库位于 sd 卡时，时间上能节省两个数量级左右。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">主要三个方法：beginTransaction，setTransactionSuccessful，endTransaction。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">SQLiteStatement</span></div></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">使用 Android 系统提供的 SQLiteStatement 来插入数据，在性能上有一定的提高，并且也解决了 SQL 注入的问题。</span></div><div style="outline: none; width: 800px; overflow: hidden; clear: both; text-align: left; margin: 5px 0px 0px 5px; padding: 0px; position: relative;"><div style="outline: none; margin: 0px; padding: 0px;"><div style="outline: none; padding: 1px; width: 100%; position: relative; overflow: auto; font-size: 1em; margin: 1em 0px; background-color: white;"><div><br/></div><table style="border-collapse: collapse; min-width: 100%;"><colgroup><col style="width: 572px;"></col><col style="width: 572px;"></col></colgroup><tbody style="word-break: break-all; border-radius: 0px; background: none; border: 0px; float: none; height: auto; left: auto; margin: 0px; outline: 0px; overflow: visible; padding: 0px; position: static; right: auto; text-align: left; top: auto; vertical-align: baseline; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto;"><tr style="word-break: break-all; border-radius: 0px; background: none; float: none; left: auto; outline: 0px; overflow: visible; padding: 0px; position: static; right: auto; text-align: left; top: auto; vertical-align: baseline; width: auto; box-sizing: content-box; font-size: 1em;"><td style="word-break: break-all; border-radius: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; vertical-align: baseline; box-sizing: content-box; font-size: 1em; outline: 0px; text-align: left; width: 572px; padding: 8px; border: 1px solid;"><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); padding: 0px 0.5em 0px 1em; text-align: right;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); bottom: auto; color: rgb(159, 184, 204); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">1</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); padding: 0px 0.5em 0px 1em; text-align: right;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); bottom: auto; color: rgb(159, 184, 204); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">2</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); padding: 0px 0.5em 0px 1em; text-align: right;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); bottom: auto; color: rgb(159, 184, 204); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">3</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); padding: 0px 0.5em 0px 1em; text-align: right;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); bottom: auto; color: rgb(159, 184, 204); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">4</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); padding: 0px 0.5em 0px 1em; text-align: right;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); bottom: auto; color: rgb(159, 184, 204); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">5</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); padding: 0px 0.5em 0px 1em; text-align: right;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border-width: 0px 3px 0px 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; border-right-style: solid; border-right-color: rgb(108, 226, 108); bottom: auto; color: rgb(159, 184, 204); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">6</span></div></td><td style="word-break: break-all; border-radius: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; top: auto; vertical-align: baseline; box-sizing: content-box; font-size: 1em; outline: 0px; text-align: left; width: 572px; padding: 8px; border: 1px solid;"><div style="word-break: break-all; border-radius: 0px; background: none; border: 0px; float: none; height: auto; left: auto; overflow: visible; right: auto; text-align: left; top: auto; vertical-align: baseline; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; padding: 0px; outline: 0px; position: relative;"><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; text-align: left; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; padding: 0px 1em;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">SQLiteStatement statement = dbOpenHelper.getWritableDatabase().compileStatement(</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: blue; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">&quot;INSERT INTO EMPERORS(name, dynasty, start_year) values(?,?,?)&quot;</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">);</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; text-align: left; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; padding: 0px 1em;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">statement.clearBindings();</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; text-align: left; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; padding: 0px 1em;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">statement.bindString(</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: rgb(0, 153, 0); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">1</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">,</span> <span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: blue; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">&quot;Max&quot;</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">);</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; text-align: left; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; padding: 0px 1em;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">statement.bindString(</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: rgb(0, 153, 0); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">2</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">,</span> <span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: blue; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">&quot;Luk&quot;</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">);</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; text-align: left; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; padding: 0px 1em;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">statement.bindString(</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: rgb(0, 153, 0); font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">3</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">,</span> <span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: blue; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">&quot;1998&quot;</span><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">);</span></div><div style="word-break: break-all; border-radius: 0px; background: none rgb(252, 252, 252); border: 0px; float: none; height: auto; left: auto; overflow: visible; position: static; right: auto; text-align: left; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; margin: 0px; outline: 0px; white-space: pre; padding: 0px 1em;background-color:initial;"><span style="word-break: break-all; border-radius: 0px; background: none; border: 0px; height: auto; overflow: visible; position: static; right: auto; top: auto; width: auto; box-sizing: content-box; font-size: 1em; min-height: auto; outline: 0px; white-space: pre; clear: both; bottom: auto; color: black; font-family: Consolas, &quot;Bitstream Vera Sans Mono&quot;, &quot;Courier New&quot;, Courier, monospace; font-stretch: normal; font-variant: normal; line-height: 1.1em;">statement.executeInsert();</span></div></div></td></tr></tbody></table><div><br/></div></div></div></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">SQLiteStatement 只能插入一个表中的数据，在插入前要清除上一次的数据。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">索引</span></div></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">索引就像书本的目录，目录可以快速找到所在页数，数据库中索引可以帮助快速找到数据，而不用全表扫描，合适的索引可以大大提高数据库查询的效率。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">优点：大大加快了数据库检索的速度，包括对单表查询、连表查询、分组查询、排序查询。经常是一到两个数量级的性能提升，且随着数据数量级增长。</span></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">缺点：</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">索引的创建和维护存在消耗，索引会占用物理空间，且随着数据量的增加而增加。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">在对数据库进行增删改时需要维护索引，所以会对增删改的性能存在影响。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">分类</span></div></div><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">1、直接创建索引和间接创建索引</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">直接创建: 使用 sql 语句创建，Android 中可以在 SQLiteOpenHelper 的 onCreate 或是 onUpgrade 中直接 excuSql 创建语句，如</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">CREATE INDEX mycolumn_index ON mytable (myclumn)</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">间接创建: 定义主键约束或者唯一性键约束，可以间接创建索引，主键默认为唯一索引。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">2、普通索引和唯一性索引</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">普通索引：</span><span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">CREATEINDEXmycolumn_indexONmytable(myclumn)</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用，语句为</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">3、单个索引和复合索引</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">单个索引：索引建立语句中仅包含单个字段，如上面的普通索引和唯一性索引创建示例。</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">复合索引：又叫组合索引，在索引建立语句中同时包含多个字段，如</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">CREATEINDEXname_indexONusername(firstname,lastname)，</span><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">其中 firstname 为前导列。</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">4、聚簇索引和非聚簇索引 (聚集索引，群集索引)</span></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列，如</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW</span><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">，其中</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">WITH ALLOW_DUP_ROW</span> <span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">表示允许有重复记录的聚簇索引</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">非聚簇索引：</span><span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">CREATEUNCLUSTEREDINDEXmycolumn_cindexONmytable(mycolumn)，</span><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">索引默认为非聚簇索引</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">使用场景</span></div></div><ul style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">当某字段数据更新频率较低，查询频率较高，经常有范围查询</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">(&gt;, &lt;, =,&gt;=, &lt;=)</span> <span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">或</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">order by</span><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">、</span><span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">group by</span> <span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">发生时建议使用索引。并且选择度（一个字段中唯一值的数量 / 总的数量）越大，建索引越有优势</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">经常同时存取多列，且每列都含有重复值可考虑建立复合索引</span></div></li></ul><div style="outline: none; margin: 0px; padding: 3px 0px;"><div><span style="font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">使用规则</span></div></div><ol style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">对于复合索引，把使用最频繁的列做为前导列 (索引中第一个字段)。如果查询时前导列不在查询条件中则该复合索引不会被使用。如</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">create unique index PK_GRADE_CLASS on student (grade, class)</span><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">，</span><span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">select * from student where class = 2</span> <span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">未使用到索引，</span><span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">select * from dept where grade = 3</span> <span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">使用到了索引</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">避免对索引列进行计算，对 where 子句列的任何计算如果不能被编译优化，都会导致查询时索引失效</span> <span style="font-size: 14px; background: rgb(249, 242, 244); outline: none; width: 640px; clear: both; border-radius: 0px; border: 1px solid rgb(204, 204, 204); color: rgb(199, 37, 78); font-family: Menlo, Monaco, Consolas, &quot;Courier New&quot;, monospace; font-stretch: normal; font-variant: normal; line-height: 1.5;">select * from student where tochar(grade)='2</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">比较值避免使用 NULL</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">多表查询时要注意是选择合适的表做为内表。连接条件要充份考虑带有索引的表、行数多的表，内外表的选择可由公式：外层表中的匹配行数 * 内层表中每一次查找的次数确定，乘积最小为最佳方案。实际多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">查询列与索引列次序一致</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">用多表连接代替 EXISTS 子句</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">把过滤记录数最多的条件放在最前面</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">善于使用存储过程，它使 sql 变得更加灵活和高效 (Sqlite 不支持存储过程)</span></div></li></ol><div style="outline: none; margin: 0px; padding: 3px 0px;"><span style="outline: none; font-size: 16px; color: rgb(255, 0, 0); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; font-weight: bold; line-height: 30px;">其它通用优化</span></div><ol style="outline: none; word-break: break-word; margin: 0px 0px 0px 25px; padding: 0.3em 0px; list-style: decimal;"><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">经常用的数据读取后缓存起来，以免多次重复读写造成“写入放大”</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">子线程读写数据</span></div></li><li style="outline:none;list-style:none;margin:0px;padding:0px;list-style-type:disc;"><div><span style="font-size: 16px; color: rgb(34, 34, 34); font-family: tahoma, arial, &quot;Microsoft YaHei&quot;; font-stretch: normal; font-variant: normal; line-height: 30px;">ObjectOutputStream 在序列化磁盘时，会把内存中的每个对象保存到磁盘，在保存对象的 时候，每个数据成员会带来一次 I/O 操作。在 ObjectOutputStream 上面再封装一个输出流 ByteArrayOutputStream 或 BufferedOutputStream，先将对象序列化后的信息写到缓存区中，然后再一次性地写到磁盘上；相应的，用 ByteArrayInputStream 或 BufferedInputStream 替代 ObjectInputStream。</span></div></li></ol><div><br/></div><div style="outline: none; text-align: left; overflow: hidden; clear: both; margin: 0px; padding: 10px 0px 0px;"><h4 style="outline: none; margin: 0px; padding: 0px; font-size: 16px;"><br/></h4></div></div></div></div></div></div></div></div></div><div><br/></div></div></div></div></div></div></div></div></div></div></div></div><div><br/></div></span>
</div></body></html> 